package com.fare.taluo.application.impl;


import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import jxl.Workbook;
import jxl.format.Alignment;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.Colour;
import jxl.format.VerticalAlignment;
import jxl.write.Label;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;

import org.apache.hadoop.hbase.client.ResultScanner;
import org.foreveross.hbase.client.HBaseHelper;
import org.foreveross.hbase.client.HBasePlaneinfoHelper;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.context.annotation.Scope;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import com.fare.taluo.application.CaptureDataExportApplication;
import com.fare.taluo.application.plane.channel.SourceApplication;
import com.fare.taluo.conf.system.DefaultPropertiesLoader;
import com.fare.taluo.util.DateUtils;
import com.fare.taluo.vo.core.PlaneInfo;
import com.fare.taluo.vo.core.SourceEntityVO;
import com.opensymphony.oscache.util.StringUtil;

@Transactional
@Scope("singleton")
@Service("captureDataExportApplicationImpl")
public class CaptureDataExportApplicationImpl implements CaptureDataExportApplication{
	private static final Logger logger = LoggerFactory.getLogger(CaptureDataExportApplicationImpl.class);
	@Resource(name="sourceApplication")
	private SourceApplication sourceApplication;
	private static List<SourceEntityVO> seList;
	
	/**
	 * 设置excel表头
	 * */
	private WritableSheet setHead(WritableSheet ws)throws Exception{
		jxl.write.WritableFont titleFont = new jxl.write.WritableFont(
                WritableFont.createFont("宋体"), 10, WritableFont.BOLD);
		jxl.write.WritableCellFormat titleFormat = new jxl.write.WritableCellFormat(titleFont);
        titleFormat.setBorder(Border.ALL, BorderLineStyle.THIN); // 线条
        titleFormat.setVerticalAlignment(VerticalAlignment.CENTRE); // 垂直对齐
        titleFormat.setAlignment(Alignment.CENTRE); // 水平对齐
        titleFormat.setWrap(true); // 是否换行
        titleFormat.setBackground(Colour.GRAY_25);// 背景色暗灰-25%
        
        ws.setColumnView(0, 20);
        Label label19 = new Label(0,0,"抓取时间",titleFormat);
        ws.addCell(label19);
        
//        ws.setColumnView(1, 15);
//        Label label1 = new Label(1,0,"代理商名字",titleFormat);
//        ws.addCell(label1);
//        ws.setColumnView(2, 10);
//        Label label2 = new Label(2,0,"区域ID",titleFormat);
//        ws.addCell(label2);
        ws.setColumnView(1, 10);
        Label label3 = new Label(1,0,"区域名",titleFormat);
        ws.addCell(label3);
        
        ws.setColumnView(2, 15);
        Label label5 = new Label(2,0,"运营商全名",titleFormat);
        ws.addCell(label5);
        
        ws.setColumnView(3, 15);
        Label label6 = new Label(3,0,"运营商英文简称",titleFormat);
        ws.addCell(label6);
        
        ws.setColumnView(4, 15);
        Label label7 = new Label(4,0,"运营商中文简称",titleFormat);
        ws.addCell(label7);
        
        ws.setColumnView(5, 15);
        Label label12 = new Label(5,0,"航班批次ID",titleFormat);
        ws.addCell(label12);
        
//        ws.setColumnView(6, 12);
//        Label label8 = new Label(6,0,"航班批次",titleFormat);
//        ws.addCell(label8);
        ws.setColumnView(6, 15);
        Label label9 = new Label(6,0,"航班批次日期",titleFormat);
        ws.addCell(label9);
        
        ws.setColumnView(7, 20);
        Label label11 = new Label(7,0,"航班预计起飞时间",titleFormat);
        ws.addCell(label11);
        
        ws.setColumnView(8, 20);
        Label label10 = new Label(8,0,"航班预计到达时间",titleFormat);
        ws.addCell(label10);
        
//        ws.setColumnView(10, 10);
//        Label label4 = new Label(10,0,"仓位信息",titleFormat);
//        ws.addCell(label4);
        
        ws.setColumnView(9, 15);
        Label label13 = new Label(9,0,"起飞城市三字码",titleFormat);
        ws.addCell(label13);
        
        ws.setColumnView(10, 15);
        Label label14 = new Label(10,0,"起飞城市名",titleFormat);
        ws.addCell(label14);
        
        ws.setColumnView(11, 15);
        Label label27 = new Label(11,0,"到达城市三字码",titleFormat);
        ws.addCell(label27);
        
        ws.setColumnView(12, 15);
        Label label28 = new Label(12,0,"到达城市名",titleFormat);
        ws.addCell(label28);
        
        ws.setColumnView(13, 10);
        Label label15 = new Label(13,0,"机型",titleFormat);
        ws.addCell(label15);
        
        ws.setColumnView(14, 10);
        Label label16 = new Label(14,0,"最高价格",titleFormat);
        ws.addCell(label16);
        
        ws.setColumnView(15, 10);
        Label label17 = new Label(15,0,"最低价格",titleFormat);
        ws.addCell(label17);
        
//        ws.setColumnView(20, 15);
//        Label label21 = new Label(20,0,"运营商英文简称(返程)",titleFormat);
//        ws.addCell(label21);
//        ws.setColumnView(21, 15);
//        Label label22 = new Label(21,0,"运营商中文简称(返程)",titleFormat);
//        ws.addCell(label22);
//        ws.setColumnView(22, 15);
//        Label label23 = new Label(22,0,"航班批次日期(返程)",titleFormat);
//        ws.addCell(label23);
//        ws.setColumnView(23, 15);
//        Label label24 = new Label(23,0,"航班预计到达时间(返程)",titleFormat);
//        ws.addCell(label24);
//        ws.setColumnView(24, 15);
//        Label label25 = new Label(24,0,"航班预计起飞时间(返程)",titleFormat);
//        ws.addCell(label25);
		return ws;
	}
	
	
	
	
	/**
	 * @see CaptureDataExportApplicationImpl#exportExcel(HttpServletResponse, String, String)
	 * */
	public void exportExcel(HttpServletResponse response,HttpServletRequest request,String grapDate,String channels)throws Exception{
		Map<String,List<PlaneInfo>> exportData = getCaptureDateGroupByArea(grapDate, channels);
		if(null == exportData) throw new Exception("nodataerror");
		boolean flag = false;
		if(exportData.keySet().size()==0)throw new Exception("nodataerror");
		for(String data : exportData.keySet()){
			List<PlaneInfo> list = exportData.get(data);
			if(list == null || list.size() == 0){
				flag = true;
			}else{
				flag = false;
				break;
			}
			
		}
		if(flag){
			throw new Exception("nodataerror");
		}
		
		String fileName = channels+"("+DateUtils.formatDay(new Date())+").xls";
	
		
		OutputStream os = response.getOutputStream();
		response.reset();// 清空输出流   
		if (request.getHeader("User-Agent").toLowerCase().indexOf("firefox") > 0){
			fileName = new String(fileName.getBytes("UTF-8"), "ISO8859-1");//firefox浏览器
		}else{
			fileName = URLEncoder.encode(fileName, "UTF-8");//其他浏览器
		}
		response.setHeader("Content-Disposition", "attachment; filename="+ fileName);// 设定输出文件头  
		response.setContentType("application/msexcel");// 定义输出类型 
		
		WritableWorkbook wwb = null;
		try{
	        wwb = Workbook.createWorkbook(os); // 建立excel文件   
	        //定义样式
	        jxl.write.WritableFont normalFont = new jxl.write.WritableFont(WritableFont.createFont("宋体"), 10);
		    jxl.write.WritableCellFormat  normalFormat = new jxl.write.WritableCellFormat(normalFont);
		    normalFormat.setBorder(Border.ALL, BorderLineStyle.THIN); 
		    normalFormat.setVerticalAlignment(VerticalAlignment.CENTRE); 
		    normalFormat.setAlignment(Alignment.CENTRE); 
		    normalFormat.setWrap(false);
	        
		    SimpleDateFormat sdf =   new SimpleDateFormat("yyyyMMddHHmm");
		    
		    SimpleDateFormat sdfType= new SimpleDateFormat("yyyy-MM-dd HH:mm");
		    
	        int index = -1;
	        for(String data : exportData.keySet()){
	        	if(null==data || "0".equals(data)){
	        		continue;
	        	}
	        	index ++;
	        	WritableSheet ws = wwb.createSheet(data, index); // sheet名称  
	        	ws = setHead(ws);
	        	List<PlaneInfo> piList = exportData.get(data);
	        	int row = 0;
	        	for(PlaneInfo pi : piList){
	        		row ++;
	        		String grabDate = sdfType.format(sdf.parse(pi.getGrabDate().substring(0, 12)));
	        		ws.addCell(new Label(0,row,grabDate,normalFormat));
//	        		ws.addCell(new Label(1,row,pi.getGrapDate(),normalFormat));
//	        		ws.addCell(new Label(2,row,pi.getAreaId(),normalFormat));
	        		ws.addCell(new Label(1,row,pi.getAreaName(),normalFormat));
	        		ws.addCell(new Label(2,row,pi.getCarrierFullName(),normalFormat));
	        		ws.addCell(new Label(3,row,pi.getCarrierKey(),normalFormat));
	        		ws.addCell(new Label(4,row,pi.getCarrierName(),normalFormat));
	        		ws.addCell(new Label(5,row,pi.getFlightId(),normalFormat));
	        //		ws.addCell(new Label(6,row,pi.getFlightBatch(),normalFormat));
	        		ws.addCell(new Label(6,row,pi.getFlightBatchDate(),normalFormat));
	        		String startTime =sdfType.format(sdf.parse(pi.getFlightBatchStartTime().substring(0, 12)));
	        		ws.addCell(new Label(7,row,startTime,normalFormat));
	        		String endTime =sdfType.format(sdf.parse(pi.getFlightBatchEndTime().substring(0, 12)));
	        		ws.addCell(new Label(8,row,endTime,normalFormat));
	        	//	ws.addCell(new Label(10,row,pi.getCabinInfos(),normalFormat));
	        		ws.addCell(new Label(9,row,pi.getFromCity(),normalFormat));
	        		ws.addCell(new Label(10,row,pi.getFromCityName(),normalFormat));
	        		ws.addCell(new Label(11,row,pi.getToCity(),normalFormat));
	        		ws.addCell(new Label(12,row,pi.getToCityName(),normalFormat));
	        		ws.addCell(new Label(13,row,pi.getType(),normalFormat));
	        		ws.addCell(new Label(14,row,pi.getHighPrice(),normalFormat));
	        		ws.addCell(new Label(15,row,pi.getLowprPrice(),normalFormat));
//	        		ws.addCell(new Label(20,row,pi.getRoundCarrierKey(),normalFormat));
//	        		ws.addCell(new Label(21,row,pi.getRoundCarrierName(),normalFormat));
//	        		ws.addCell(new Label(22,row,pi.getRoundFlightBatchDate(),normalFormat));
//	        		ws.addCell(new Label(23,row,pi.getRoundFlightBatchEndTime(),normalFormat));
//	        		ws.addCell(new Label(24,row,pi.getRoundFlightBatchStartTime(),normalFormat));
	        	}
	        }
	        wwb.write();
		}catch(Exception e){
			e.printStackTrace();
			logger.error("",e);
			throw new Exception(e);
		}finally{
			try {
				wwb.close();
				os.close();
			} catch (Exception e) {
				e.printStackTrace();
				logger.error("",e);
				throw new Exception(e);
			}  
		}
	}
	
	/**
	 * 获得抓取数据按区域分组
	 * */
	private Map<String,List<PlaneInfo>> getCaptureDateGroupByArea(String grapDate,String channels){
		Map<String,List<PlaneInfo>> map = new HashMap<String, List<PlaneInfo>>();
		if(StringUtil.isEmpty(grapDate) || StringUtil.isEmpty(channels))
		return null;
		grapDate = grapDate.replace("-", "").replace(" ", "");
		if(seList == null){
			seList = sourceApplication.findAllSourceEntity();
		}
		//网络名抓换成Key
		String key = channels;
		
		if(StringUtil.isEmpty(key)) return null;
		
		//查询抓取数据
		ResultScanner rs = null;
		List<PlaneInfo>  list = null;
		try {
			rs = HBaseHelper.getRecords("CRAWL_PLANE_FREIGHT", grapDate+key+"0000000000000000000000000000000", grapDate+key+"ZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZ");
			list = HBasePlaneinfoHelper.transformHBaseResultToPlaneinfo(rs);
		} catch (IOException e) {
			e.printStackTrace();
			logger.debug("",e);
		}finally{
			if(rs!=null)
			rs.close();
		}
		//按地区拆分
		Map<String,String> groups = new HashMap<String,String>();
		for(PlaneInfo pi : list){
			if(StringUtil.isEmpty(pi.getAreaName()))
				continue;
			groups.put(pi.getAreaName(), "");
		}
		for(String area : groups.keySet()){
			List<PlaneInfo>  detailList = new ArrayList<PlaneInfo>();
			for(PlaneInfo pi : list){
				if(area.equalsIgnoreCase(pi.getAreaName())){
					detailList.add(pi);
				}
			}
			map.put(grapDate+"-"+channels+"-"+area, detailList);
		}
		
		return map;
	}

	@Override
	public String exportExcelByTime(Date date, String code,String fileName) throws Exception {
		SimpleDateFormat sdfType= new SimpleDateFormat("yyyy-MM-dd HH");
		String grapDate = sdfType.format(date);
		Map<String,List<PlaneInfo>> exportData = getCaptureDateGroupByArea(grapDate, code);
		if(null == exportData || exportData.isEmpty() || exportData.size()<=0){
			return "nodata";
		}
		boolean flag = false;
		for(String data : exportData.keySet()){
			List<PlaneInfo> list = exportData.get(data);
			if(list == null || list.size() == 0){
				flag = true;
			}else{
				flag = false;
				break;
			}
			
		}
		if(flag){
			return "nodata";
		}
		
//		String dateType =sdfType.format(date);
//		String fileName1 =code+'('+dateType+')';
	
		//	ReportProperties rp=ReportProperties.getInstance();
			File f =new File(DefaultPropertiesLoader.getAppExcelFilePath()+"/"+fileName+".xls");
			if(f.exists()){
				System.out.print(1);
			}
			OutputStream os=new FileOutputStream(f);
			WritableWorkbook wwb =null;
		//	wwb=Workbook.createWorkbook(os);
		try {
			 wwb = Workbook.createWorkbook(os); // 建立excel文件   
		        //定义样式
		        jxl.write.WritableFont normalFont = new jxl.write.WritableFont(WritableFont.createFont("宋体"), 10);
			    jxl.write.WritableCellFormat  normalFormat = new jxl.write.WritableCellFormat(normalFont);
			    normalFormat.setBorder(Border.ALL, BorderLineStyle.THIN); 
			    normalFormat.setVerticalAlignment(VerticalAlignment.CENTRE); 
			    normalFormat.setAlignment(Alignment.CENTRE); 
			    normalFormat.setWrap(false);
		        
			    SimpleDateFormat sdf1 =   new SimpleDateFormat("yyyyMMddHHmm");
			    
			    SimpleDateFormat sdfType1= new SimpleDateFormat("yyyy-MM-dd HH:mm");
			    
		        int index = -1;
		        for(String data : exportData.keySet()){
		        	index ++;
		        	WritableSheet ws = wwb.createSheet(data, index); // sheet名称  
		        	ws = setHead(ws);
		        	List<PlaneInfo> piList = exportData.get(data);
		        	int row = 0;
		        	for(PlaneInfo pi : piList){
		        		row ++;
		        		String grabDate = sdfType1.format(sdf1.parse(pi.getGrabDate().substring(0, 12)));
		        		ws.addCell(new Label(0,row,grabDate,normalFormat));
//		        		ws.addCell(new Label(1,row,pi.getGrapDate(),normalFormat));
//		        		ws.addCell(new Label(2,row,pi.getAreaId(),normalFormat));
		        		ws.addCell(new Label(1,row,pi.getAreaName(),normalFormat));
		        		ws.addCell(new Label(2,row,pi.getCarrierFullName(),normalFormat));
		        		ws.addCell(new Label(3,row,pi.getCarrierKey(),normalFormat));
		        		ws.addCell(new Label(4,row,pi.getCarrierName(),normalFormat));
		        		ws.addCell(new Label(5,row,pi.getFlightId(),normalFormat));
		      //  		ws.addCell(new Label(6,row,pi.getFlightBatch(),normalFormat));
		        		ws.addCell(new Label(6,row,pi.getFlightBatchDate(),normalFormat));
		        		String startTime =null;
		        		if(pi.getFlightBatchStartTime()!=null && pi.getFlightBatchStartTime().length()>=12){
		        			startTime=	sdfType1.format(sdf1.parse(pi.getFlightBatchStartTime().substring(0, 12)));
		        		}
		        		ws.addCell(new Label(7,row,startTime,normalFormat));
		        		String endTime =null;
		        		if(pi.getFlightBatchEndTime()!=null && pi.getFlightBatchEndTime().length()>=12){
		        			endTime=sdfType1.format(sdf1.parse(pi.getFlightBatchEndTime().substring(0, 12)));
		        		}else{
		        			endTime=pi.getFlightBatchEndTime();
		        		}
		        		ws.addCell(new Label(8,row,endTime,normalFormat));
		       // 		ws.addCell(new Label(10,row,pi.getCabinInfos(),normalFormat));
		        		ws.addCell(new Label(9,row,pi.getFromCity(),normalFormat));
		        		ws.addCell(new Label(10,row,pi.getFromCityName(),normalFormat));
		        		ws.addCell(new Label(11,row,pi.getToCity(),normalFormat));
		        		ws.addCell(new Label(12,row,pi.getToCityName(),normalFormat));
		        		ws.addCell(new Label(13,row,pi.getType(),normalFormat));
		        		ws.addCell(new Label(14,row,pi.getHighPrice(),normalFormat));
		        		ws.addCell(new Label(15,row,pi.getLowprPrice(),normalFormat));
//		        		ws.addCell(new Label(20,row,pi.getRoundCarrierKey(),normalFormat));
//		        		ws.addCell(new Label(21,row,pi.getRoundCarrierName(),normalFormat));
//		        		ws.addCell(new Label(22,row,pi.getRoundFlightBatchDate(),normalFormat));
//		        		ws.addCell(new Label(23,row,pi.getRoundFlightBatchEndTime(),normalFormat));
//		        		ws.addCell(new Label(24,row,pi.getRoundFlightBatchStartTime(),normalFormat));
		        	}
		        }
		        wwb.write();
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			try {
				wwb.close();
				os.close();
			} catch (Exception e) {
				e.printStackTrace();
				logger.error("",e);
				throw new Exception(e);
			}  
		}
		return f.getPath();
	}
	
}
